/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Logic;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

/**
 *
 * @author David-PC
 */
public class Filtro {
    

    private boolean allDates;
    private boolean allOperations;
    private boolean allTables;
    private boolean allUsers;
    private boolean checkRules;
    private ArrayList<String> operation;
    private Date fechaDesde;
    private Date fechaHasta;
    private ArrayList<String> usuarios;
    private ArrayList<String> tablas;
    ArrayList<String> archives;
    public static String dateFormatString = "dd/MM/yy hh:mm a";
    public DateFormat dateFormat = new SimpleDateFormat(dateFormatString);

    public Filtro(boolean allDates, boolean allOperations, boolean allTables, boolean allUsers, boolean checkRules, ArrayList<String> operation, Date fechaDesde, Date fechaHasta, ArrayList<String> usuarios, ArrayList<String> tablas) {
        this.allDates = allDates;
        this.allOperations = allOperations;
        this.allTables = allTables;
        this.allUsers = allUsers;
        this.checkRules = checkRules;
        this.operation = operation;
        this.fechaDesde = fechaDesde;
        this.fechaHasta = fechaHasta;
        this.usuarios = usuarios;
        this.tablas = tablas;
    }

    public String queryUsuarios(){
        
        String username = "1=1 \n";
        
        if(!allUsers){
            if(usuarios.size()>0){
                username = "( (username = '"+usuarios.get(0)+"')";// AND SEG_OWNER ='"+usuarios.get(0)+"') ";
                
                for(int i=1;i<usuarios.size();i++){
                    username+="OR ";
                    username += "(username = '"+usuarios.get(i)+"')";// AND SEG_OWNER ='"+usuarios.get(i)+"') ";
                }
                username += ") \n";
            }
            
        }
        return username;
    }
    public String queryFecha(){
        
        String fecha = "1=1 \n";
//        
        if(!allDates){
            fecha = "( to_date('"+dateFormat.format(fechaDesde)+"', 'dd/MM/yy hh:mi AM') <= to_date(TIMESTAMP, 'dd/MM/yy hh:mi AM') AND "
                    +" to_date('"+dateFormat.format(fechaHasta)+"', 'dd/MM/yy hh:mi AM') >= to_date(TIMESTAMP, 'dd/MM/yy hh:mi AM')) \n";
        }
//        if(!allDates){
//            fecha = "("+fechaDesde.getTime()+" <= (TIMESTAMP, 'dd/MM/yy hh:mm AM') AND "+
//                    "to_char("+fechaHasta.getTime()+", 'dd/MM/yy hh:mm AM') <= to_char(TIMESTAMP, 'dd/MM/yy hh:mm AM')) \n";
//        }
        return fecha;
    }
    public String queryOperation(){
       String op;
       op = " (operation = '" + operation.get(0)+"'";
       for(int i=1;i<operation.size();i++){
           op += " OR operation = '" + operation.get(i)+"' ";
       }
       op+=") ";
       
       return op;
    }
    public String queryTablas(){
        
        String tables = "1=1 ";
        
        if(!allTables){
            if(tablas.size()>0){
                tables = "(TABLE_NAME = '"+tablas.get(0)+"' ";
                
                for(int i=1;i<tablas.size();i++){
                    tables+="OR ";
                    tables += "TABLE_NAME = '"+tablas.get(i)+"' ";
                }
                tables += ") ";
            }
        }
        return tables;
    }
    public String crearLogminerQuery(){
        
        String query="";
        String username = queryUsuarios();
        String fecha = queryFecha();
        String ope = queryOperation();
        String tab= queryTablas();
        
        query+= "SELECT " +
                "to_char(TIMESTAMP, 'dd/MM/yy hh:mi AM')," +
                "username," +
                "operation," +
                "TABLE_SPACE," +
                "TABLE_NAME," +
                "sql_redo " +
                "FROM " +
                "v$logmnr_contents " +
                "WHERE "+
                username + " AND "+
                fecha + " AND "+
                ope + " AND "+
                tab;
        
        return query;
    }
    public String logMinerBuild(){
        
        String script="";
        script+="  DBMS_LOGMNR_D.build (\n" +
                "    dictionary_filename => 'TSH1dict.ora',\n" +
                "    dictionary_location => 'C:\\oraclexe\\app\\oracle\\flash_recovery_area\\XE\\ARCHIVELOG');\n";
        
        return script;
    }
    public String logminerAddLogFiles(){
        
        String script="";       
        script+="  DBMS_LOGMNR.add_logfile (\n" +
                "    options     => DBMS_LOGMNR.new,\n" +
                "    logfilename => '";
        
        script+=archives.get(0);
        
        script+="');";
        
        for(int i=1;i<archives.size();i++){
            
            script+="DBMS_LOGMNR.add_logfile (\n" +
                "    options     => DBMS_LOGMNR.addfile,\n" +
                "    logfilename => '";
            script+=archives.get(i);
            script+="');";
        }
        
        return script;
    }
    public String logminerStart(){
        String script="";   
        script+=
                "  DBMS_LOGMNR.start_logmnr (\n" +
                "    dictfilename => 'C:\\oraclexe\\app\\oracle\\flash_recovery_area\\XE\\ARCHIVELOG\\TSH1dict.ora');\n";
        return script;    
        
    }
    
    public String crearLogminerScript(){
        String script="";   
        script+="BEGIN\n" +
                logMinerBuild()+
                logminerAddLogFiles()+
                logminerStart()+
                "END;\n";
        return script;   
    }

    @Override
    public String toString() {
        String str=crearLogminerQuery();
        
        return str;
    }
    
    

    public ArrayList<String> getOperation() {
        return operation;
    }

    public void setOperation(ArrayList<String> operation) {
        this.operation = operation;
    }
    
    

    public void setArchives(ArrayList<String> archives) {
        this.archives = archives;
    }

    public ArrayList<String> getArchives() {
        return archives;
    }
    
    /**
     * @return the allDates
     */
    public boolean isAllDates() {
        return allDates;
    }

    /**
     * @param allDates the allDates to set
     */
    public void setAllDates(boolean allDates) {
        this.allDates = allDates;
    }

    /**
     * @return the allOperations
     */
    public boolean isAllOperations() {
        return allOperations;
    }

    /**
     * @param allOperations the allOperations to set
     */
    public void setAllOperations(boolean allOperations) {
        this.allOperations = allOperations;
    }

    /**
     * @return the allTables
     */
    public boolean isAllTables() {
        return allTables;
    }

    /**
     * @param allTables the allTables to set
     */
    public void setAllTables(boolean allTables) {
        this.allTables = allTables;
    }

    /**
     * @return the allUsers
     */
    public boolean isAllUsers() {
        return allUsers;
    }

    /**
     * @param allUsers the allUsers to set
     */
    public void setAllUsers(boolean allUsers) {
        this.allUsers = allUsers;
    }

    /**
     * @return the checkRules
     */
    public boolean isCheckRules() {
        return checkRules;
    }

    /**
     * @param checkRules the checkRules to set
     */
    public void setCheckRules(boolean checkRules) {
        this.checkRules = checkRules;
    }

    /**
     * @return the fechaDesde
     */
    public Date getFechaDesde() {
        return fechaDesde;
    }

    /**
     * @param fechaDesde the fechaDesde to set
     */
    public void setFechaDesde(Date fechaDesde) {
        this.fechaDesde = fechaDesde;
    }

    /**
     * @return the fechaHasta
     */
    public Date getFechaHasta() {
        return fechaHasta;
    }

    /**
     * @param fechaHasta the fechaHasta to set
     */
    public void setFechaHasta(Date fechaHasta) {
        this.fechaHasta = fechaHasta;
    }

    /**
     * @return the usuarios
     */
    public ArrayList<String> getUsuarios() {
        return usuarios;
    }

    /**
     * @param usuarios the usuarios to set
     */
    public void setUsuarios(ArrayList<String> usuarios) {
        this.usuarios = usuarios;
    }

    /**
     * @return the tablas
     */
    public ArrayList<String> getTablas() {
        return tablas;
    }

    /**
     * @param tablas the tablas to set
     */
    public void setTablas(ArrayList<String> tablas) {
        this.tablas = tablas;
    }
    
    
}
